package sanrenxing.dayEnd.backRepertoryTotal.dao;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import sanrenxing.dayEnd.backRepertoryTotal.domain.WorthStatistics;

import java.util.List;

public interface WorthStatisticsDao {

    /**
     * 资产净值页面显示
     * @param start
     * @param end
     * @return
     */
   /* @Select("<script>" +
            "select ws.netDate as netDate,ws.TotalAssets as TotalAssets,ws.totalRespo as totalRespo,ws.netAsset as netAsset,(ws.totalRespo-ws.netAsset) as AssetNotValue from " +
            " (select rownum rn,ss.* from " +
            "(select * from worthStatistics) ss" +
            " <where>#{end}>=rownum</where>) ws " +
            "<where>rn>=#{start}</where>" +
            "</script>")*/
    @Select("select * from (select rownum r,worthStatistics.*,row_number()over(order by worthStatistics.netDate desc) from worthStatistics where rownum<=${start}) where r>=${end} ")
    public List<WorthStatistics> queryWorth(@Param("start") int start, @Param("end") int end);

    /**
     * 资产净值行数
     * @return
     */
    @Select("select count(*) from worthStatistics")
    public int count();
    @Select("select * from worthStatistics where to_char(netDate,'yyyy-MM-dd') in(${xx})")
    public List<WorthStatistics> syt(String xx);
    @Select("select * from worthStatistics where netDate>=to_date('${xx}','yyyy-MM-dd')  order by netDate")
    public List<WorthStatistics> syts(String xx);
    /**
     * 拿到证券库存中所有金额
     * @return
     */
    @Select("select nvl(sum(sumMoney),0) from securitiesStock")
    public double reStockTotalMoney();

    /**
     * 拿到现金库存中的所有金额
     * @return
     */
    @Select("select nvl(sum(caMoney),0) from cashStock")
    public double cashStockTotalMoney();

    /**
     * 从现金应收应付当中取出流入的金额
     * @return
     */
    @Select("select nvl(sum(sumMoney),0) from cashRecAndPayStock where status=1")
    public double cashRecAndPayMoney();

    /**
     * 从现金应收应付当中取出流出的金额
     * @return
     */
    @Select("select nvl(sum(sumMoney),0) from cashRecAndPayStock where status=-1")
    public double cashRecAndPayMoneyTwo();

    /**
     * 从证券应收应付当中取出流入的金额
     * @return
     */
    @Select("select nvl(sum(businStat),0) from secRecAndPayStock where businStat=1")
    public double secRecAndPayMoney();

    /**
     * 从证券应收应付当中取出流出的金额
     * @return
     */
    @Select("select nvl(sum(businStat),0) from secRecAndPayStock where businStat=-1")
    public double secRecAndPayMoneyTwo();

    @Insert("insert into worthStatistics(netDate,TotalAssets,totalRespo,netAsset,assetValue) " +
            "values(to_date('${dates}','yyyy-MM-dd'),#{totalAssets},#{totalRespo},#{netAsset},#{assetValue})")
    public void addTotal(@Param("dates") String dates,@Param("totalAssets") Double totalAssets,@Param("totalRespo") Double totalRespo,@Param("netAsset") Double netAsset,@Param("assetValue") Double assetValue);

    /**
     * 查询ta数量
     * @return
     */
    @Select("select tatNum from taStock")
    public Long queryTaNumber();

    /**
     * 取出存款的总金额
     * @return
     */
    @Select("select nvl(sum(depMoney),0) from depositBusiness where handIf=1")
    public double depositGetTotalMoney();

    /**
     * 取出证券应收应付的流入总金额
     * @return
     */
    @Select("select nvl(sum(money),0) from receivableAndPayable where capflow=1 and businType='债券利息'")
    public double recGetIntoTotalMoney();

    /**
     * 取出证券应收应付的流出总金额
     * @return
     */
    @Select("select nvl(sum(money),0) from receivableAndPayable where capflow=-1")
    public double recGetOutTotalMoney();

    /**
     * 取出现金应收应付的流出总金额
     * @return
     */
    @Select("select nvl(sum(money),0) from cashReceivable where capFlow=-1")
    public double cashGetOutTotalMoney();

    /**
     * 取出现金应收应付的流入总金额
     * @return
     */
    @Select("select nvl(sum(money),0) from cashReceivable where capFlow=1 and businType='存款利息' or businType='现金利息'")
    public double cashGetIntoTotalMoney();

    /**
     * 取出资金调拨中流入的总金额
     * @return
     */
    @Select("select nvl(sum(reMoney),0) from fundAllocation where dial=1 and mark='未处理'")
    public double fundGetIntoTotalMoney();

    /**
     * 取出资金调拨中流出的总金额
     * @return
     */
    @Select("select nvl(sum(reMoney),0) from fundAllocation where dial=-1 and mark='未处理'")
    public double fundGetOutTotalMoney();

    /**
     * 拿出增值估值的数据
     * @return
     */
    @Select("select nvl(sum(sumMoney),0) from secRecAndPayStock srap where srap.businType='估值增值'")
    public double valuationTotalMoney();

    /**
     * 取得基金总份额
     * @return
     */
    @Select("select tatNum from taStock")
    public Long shareNumber();

    /**
     * 删除
     */
    @Delete("delete from worthStatistics where to_char(netDate,'yyyy-MM-dd')=#{str}")
    public void delete(@Param("str")String str);

}
